package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JOptionPane;

import Entita.Categoria;
import Entita.Diagnosi;
import Entita.Interpretazione;
import Entita.Diagnosi;
import Entita.Paziente;
import Entita.Tavola;
import GUI.Main;

public class DiagnosiDAO
{

	Connection con=null;
	PreparedStatement ptmt=null;
	ResultSet rs=null;

	public DiagnosiDAO()
	{

	}

	private Connection getConnection() throws SQLException
	{
		Connection conn;
		conn=ConnectionFactory.getInstance().getConnection();
		return conn;
	}


	public static Diagnosi newDiagnosi(Paziente paziente,String titolo){
		Connection con=null;
		ResultSet rs=null;
		PreparedStatement stm=null;
		PreparedStatement ins=null;
		Diagnosi returnValue=null;
		try{
			String scalarString="SELECT max(ID_Diagnosi)+1 as newID from DIAGNOSI";
			con=ConnectionFactory.getInstance().getConnection();
			stm=con.prepareStatement(scalarString);
			stm.executeQuery();
			rs=stm.executeQuery();
			if(rs.next())
			{
				
				Diagnosi diagnosi= new Diagnosi();
				diagnosi.setId(rs.getInt("newID"));
				rs.close();
				con.close();
				//con=ConnectionFactory.getInstance().getConnection();
				diagnosi.setNome(titolo);
				diagnosi.setPaziente(paziente);
				DiagnosiDAO diagnosiDAO=new DiagnosiDAO();
				diagnosiDAO.add(diagnosi);
				returnValue=diagnosi;
			}else{
				return null;
			}
			
		}catch(Exception e){
			return null;
		}finally{
			try
			{
				if(rs!=null)
					rs.close();
				if(ins!=null)
					ins.close();
				if(stm!=null)
					stm.close();
				if(con!=null)
					con.close();
			}
			catch(SQLException e)
			{
				Main.showError("Errore:"+e.getMessage());
			}
			catch(Exception e)
			{
				Main.showError("Errore:"+e.getMessage());
			}
			return returnValue;
		}
		
	}
		
	
	public void add(Diagnosi diagnosi)
	{

		try
		{
			String querystring="INSERT INTO Diagnosi (ID_Diagnosi,ID_Paziente, Nome, Diagnosi, Indici)" +
					"VALUES(?,?,?,?,?)";
			con=getConnection();
			ptmt=con.prepareStatement(querystring);
			ptmt.setInt(1, diagnosi.getId());
			ptmt.setInt(2, diagnosi.getPaziente().getID());
			ptmt.setString(3, diagnosi.getNome());
			ptmt.setString(4, diagnosi.getDiagnosi());
			ptmt.setString(5, diagnosi.getIndici());
			ptmt.executeUpdate();
		}
		catch(SQLException e)
		{
			Main.showError("Errore:"+e.getMessage());
		}
		finally
		{
			try
			{
				if(rs!=null)
					rs.close();
				if(ptmt!=null)
					ptmt.close();
				if(con!=null)
					con.close();
			}
			catch(SQLException e)
			{
				Main.showError("Errore:"+e.getMessage());
			}
			catch(Exception e)
			{
				Main.showError("Errore:"+e.getMessage());
			}

		}

	}
	
	public void update(Diagnosi diagnosi)
	{

		try
		{
			String querystring="UPDATE Diagnosi SET ID_Paziente=?, Nome=?, Diagnosi=?, Indici=?" +
					"WHERE ID_Diagnosi=?";
			con=getConnection();
			ptmt=con.prepareStatement(querystring);

			ptmt.setInt(1, diagnosi.getPaziente().getID());
			ptmt.setString(2, diagnosi.getNome());
			ptmt.setString(3, diagnosi.getDiagnosi());
			ptmt.setString(4, diagnosi.getIndici());
			ptmt.setInt(5, diagnosi.getId());
			ptmt.executeUpdate();

		}
		catch(SQLException e)
		{
			Main.showError("Errore:"+e.getMessage());
		}
		finally
		{
			try
			{
				if(rs!=null)
					rs.close();
				if(ptmt!=null)
					ptmt.close();
				if(con!=null)
					con.close();
			}
			catch(SQLException e)
			{
				Main.showError("Errore:"+e.getMessage());
			}
			catch(Exception e)
			{
				Main.showError("Errore:"+e.getMessage());
			}

		}

	}

	public void delete(Diagnosi diagnosi)
	{

		try
		{
			String querystring="DELETE FROM Diagnosi WHERE ID_Diagnosi=?";
			con=getConnection();
			ptmt=con.prepareStatement(querystring);
			ptmt.setInt(1, diagnosi.getId());
			ptmt.executeUpdate();

		}
		catch(SQLException e)
		{
			Main.showError("Errore:"+e.getMessage());
		}
		finally
		{
			try
			{
				if(rs!=null)
					rs.close();
				if(ptmt!=null)
					ptmt.close();
				if(con!=null)
					con.close();
			}
			catch(SQLException e)
			{
				Main.showError("Errore:"+e.getMessage());
			}
			catch(Exception e)
			{
				Main.showError("Errore:"+e.getMessage());
			}

		}

	}

	public List<Diagnosi> findAll()
	{
		List<Diagnosi> lista=new ArrayList<Diagnosi>();
		Diagnosi diagnosi=null;
		try
		{
			String querystring="SELECT * FROM Diagnosi";
			con=getConnection();
			ptmt=con.prepareStatement(querystring);
			rs=ptmt.executeQuery();
			while(rs.next())
			{
				diagnosi=new Diagnosi();
				diagnosi.setId(rs.getInt(1));
				diagnosi.setPaziente(null);
				diagnosi.setNome(rs.getString(3));
				diagnosi.setDiagnosi(rs.getString(4));
				diagnosi.setIndici(rs.getString(5));
				lista.add(diagnosi);
			}
		}
		catch(SQLException e)
		{
			Main.showError("Errore:"+e.getMessage());
		}
		finally
		{
			try
			{
				if(rs!=null)
					rs.close();
				if(ptmt!=null)
					ptmt.close();
				if(con!=null)
					con.close();
			}
			catch(SQLException e)
			{
				Main.showError("Errore:"+e.getMessage());
			}
			catch(Exception e)
			{
				Main.showError("Errore:"+e.getMessage());
			}

		}
		return lista;
	}
	
	public ArrayList<Diagnosi> findByPaziente(Paziente paziente)
	{
		ArrayList<Diagnosi> lista=new ArrayList<Diagnosi>();
		Diagnosi diagnosi=null;
		try
		{
			String querystring="SELECT * FROM Diagnosi where ID_Paziente=?";
			con=getConnection();
			ptmt=con.prepareStatement(querystring);
			ptmt.setInt(1, paziente.getID());
			rs=ptmt.executeQuery();
			while(rs.next())
			{
				diagnosi=new Diagnosi();
				diagnosi.setId(rs.getInt(1));
				diagnosi.setPaziente(paziente);
				diagnosi.setNome(rs.getString(3));
				diagnosi.setDiagnosi(rs.getString(4));
				diagnosi.setIndici(rs.getString(5));
				lista.add(diagnosi);
			}
		}
		catch(SQLException e)
		{
			Main.showError("Errore:"+e.getMessage());
		}
		finally
		{
			try
			{
				if(rs!=null)
					rs.close();
				if(ptmt!=null)
					ptmt.close();
				if(con!=null)
					con.close();
			}
			catch(SQLException e)
			{
				Main.showError("Errore:"+e.getMessage());
			}
			catch(Exception e)
			{
				Main.showError("Errore:"+e.getMessage());
			}

		}
		return lista;
	}
	
	
	public Diagnosi findByPrimaryKey(int IdDiagnosi) throws Exception
	{

		Diagnosi diagnosi=null;
		try
		{
			String querystring="SELECT * FROM Diagnosi WHERE ID_Diagnosi=?";
			con=getConnection();
			ptmt=con.prepareStatement(querystring);
			ptmt.setInt(1, IdDiagnosi);
			rs=ptmt.executeQuery();
			if(rs.next())
			{
				diagnosi=new Diagnosi();
				diagnosi.setId(rs.getInt(1));
				diagnosi.setNome(rs.getString(3));
				diagnosi.setDiagnosi(rs.getString(4));
				diagnosi.setIndici(rs.getString(4));
			}
		}
		catch(SQLException e)
		{
			throw new SQLException(e);
		}
		finally
		{
			try
			{
				if(rs!=null)
					rs.close();
				if(ptmt!=null)
					ptmt.close();
				if(con!=null)
					con.close();
			}
			catch(SQLException e)
			{
				throw new SQLException(e);
			}
			catch(Exception e)
			{
				throw new Exception(e);
			}

		}

		return diagnosi;
	}
	

}